Metric Formulas and Thresholds

Metric formulas are defined using standard MS Excel-based formula syntax. They use array-based formulas in their calculations. The formulas enable you to group and aggregate multiple activities together so that results for a ribbon or phase or intersection can be calculated.

Note: For detailed information about writing formulas and creating metrics, see the Deltek Acumen Metric Developers Guide.
The activity metrics can include three formulas (primary, secondary, and tripwire). The Control Account and Work Package metrics can include four formulas (primary, secondary, tripwire, and secondary tripwire). Each of these formulas can be built using a three-level hierarchy:
  • Inclusions โ€” A top level set of filters to include specific data based on type, status and time period.
  • Filters โ€” Simple filters based on fields (for example, Actual Start Date ยป Baseline Start Date). Multiple filters can be added and treated as AND compounds. They can include calculated fields and project variables.
  • Formulas โ€” These are user-defined formulas. They are useful when you need OR statements, divisions, or other advanced features. They can include calculated fields and project variables.

Formulas

There are four formulas in Acumen and they all use MS Excel Array formula syntax:
  • Primary formula
  • Secondary formula
  • Tripwire formula
  • Secondary Tripwire formula

Formulas can be built within Fuse either through the (basic) freeform formula editor or by selecting functions/fields (advanced) from the various menus within the metric editor.

  • Basic is a filter-based set of metrics that doesn't require detailed formula definition.
  • Advanced uses detailed formulas to define a metric beyond a simple filter. The Check Formula feature ensures correct syntax during metric development

The easiest way to start creating advanced formulas is to first modify an existing formula. The syntax for all formulas, new or modified, follows a standard format of:

  • Function(fieldvalue)

Common functions include COUNT, IF, COUNTIF, SUM, and so on and can be used in combination.

If conditions are used, then the syntax is modified and follows the format of:
  • Function(fieldvalue,value1,value2)
It uses Value 1 if the condition is met and Value 2 if the condition is not met.

Thresholds

A tripwire threshold is a defined value that, if exceeded, causes a metric to be classified as "triggered." A metric can have multiple trigger points with corresponding color coding for each interval. The threshold editor enables customizable bandwidths or thresholds to be defined, color coded, and described.

Using Calculated Fields in Formulas

You could use a calculated field to develop an activity metric to see data in the Control Account or WAD or create a control account metric that sees, for example, the earliest baseline start of an activity.